﻿
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Windows.Forms
Imports Microsoft.Office.Interop
Imports System.IO



Public Class M3Contract


    Private dataAdapter As New OleDbDataAdapter

    '************************************************************************************************************************
    ' This view has to be createt on the server before you can use this fonction
    '************************************************************************************************************************

    'drop view M3JDTAP001.JLD012
    'GO
    'create VIEW M3JDTAP001.JLD012   AS
    'SELECT    SAHEAD.CACTNO , SAHEAD.CAAOTP ,SALINE.CCAGTP AS Typ, SALINE.CCNODT AS "Next invoice", SALINE.CCDIP1 AS "Procent 1", SALINE.CCDIP2 AS "Procent 2", SALINE.CCDIP3 AS "Procent 3", SALINE.CCDIP4 AS "Procent 4", SALINE.CCDIP5 AS "Procent 5", SALINE.CCDIP6 AS "Procent 6"
    'FROM M3JDTAP001.SAHEAD INNER JOIN M3JDTAP001.SALINE ON SAHEAD.CACONO = SALINE.CCCONO AND SAHEAD.CADIVI = SALINE.CCDIVI AND SAHEAD.CACTNO = SALINE.CCCTNO
    'WHERE SAHEAD.CAVLCD = '020' and SALINE.CCVLCD = '020'
    'Group By SALINE.CCNODT ,SALINE.CCAGTP, SALINE.CCDIP1, SALINE.CCDIP2, SALINE.CCDIP3, SALINE.CCDIP4, SALINE.CCDIP5, SALINE.CCDIP6, SAHEAD.CACTNO, SAHEAD.CAAOTP


    'create VIEW M3JDTAP001.JLD013   AS

    'SELECT    SAHEAD.CACTNO , SAHEAD.CAAOTP , SALINE.CCNODT AS "Next invoice", SALINE.CCDIP1 AS "Procent 1", SALINE.CCDIP2 AS "Procent 2", SALINE.CCDIP3 AS "Procent 3", SALINE.CCDIP4 AS "Procent 4", SALINE.CCDIP5 AS "Procent 5", SALINE.CCDIP6 AS "Procent 6"
    'FROM M3JDTAP001.SAHEAD INNER JOIN M3JDTAP001.SALINE ON SAHEAD.CACONO = SALINE.CCCONO AND SAHEAD.CADIVI = SALINE.CCDIVI AND SAHEAD.CACTNO = SALINE.CCCTNO
    'WHERE SAHEAD.CAVLCD = '020' and SALINE.CCVLCD = '020'
    'Group By SALINE.CCNODT , SALINE.CCDIP1, SALINE.CCDIP2, SALINE.CCDIP3, SALINE.CCDIP4, SALINE.CCDIP5, SALINE.CCDIP6, SAHEAD.CACTNO, SAHEAD.CAAOTP






    Function GetData(ByVal selectCommand As String, ByRef Frame As String)

        Try

            Dim ConCM As OleDb.OleDbConnection
            ConCM = New OleDb.OleDbConnection(My.Settings.M3Connect)
            ConCM.Open()

            Dim cmd As New OleDbCommand(selectCommand, ConCM)

            Me.dataAdapter = New OleDb.OleDbDataAdapter(cmd)
            Dim table As New DataTable
            table.Locale = System.Globalization.CultureInfo.InvariantCulture
            Me.dataAdapter.Fill(table)
            ConCM.Close()

            If Frame = "1" Then
                Me.BindingSource1.DataSource = table
                ' Resize the GridView columns to fit the newly loaded content.
                Me.GridView1.BestFitColumns()
                If File.Exists(My.Settings.ConfigPath & "\M3ContactW01.xml") Then GridView1.RestoreLayoutFromXml(My.Settings.ConfigPath & "\M3ContactW01.xml")

            Else
                Me.BindingSource2.DataSource = table
                ' Resize the GridView columns to fit the newly loaded content.
                Me.GridView2.BestFitColumns()
                If File.Exists(My.Settings.ConfigPath & "\M3ContactW02.xml") Then GridView2.RestoreLayoutFromXml(My.Settings.ConfigPath & "\M3ContactW02.xml")

            End If

        Catch ex As OleDbException

        End Try
        GetData = ""

    End Function



    Private Sub BarButtonItem1_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem1.ItemClick

        Dim q As String
        q = ControlChars.Quote
        Application.DoEvents()

        GetData("select  count(*) as Status ,cactno as Contract, caaotp as Typ from JLD013 group by cactno, caaotp order by count(*) desc", "1")



    End Sub

    Private Sub BarButtonItem2_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem2.ItemClick

        GetData("select * from JLD012 ", "2")


    End Sub

    Private Sub M3Contract_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        My.Settings.M3ContractSize = Me.Size
        My.Settings.M3ContractPos = Me.Location

        GridView1.SaveLayoutToXml(My.Settings.ConfigPath & "\M3ContactW01.xml")
        GridView2.SaveLayoutToXml(My.Settings.ConfigPath & "\M3ContactW02.xml")

    End Sub

    
    Private Sub M3Contract_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        Me.Size = My.Settings.M3ContractSize
        Me.Location = My.Settings.M3ContractPos
        Me.SplitContainerControl1.SplitterPosition = My.Settings.M3ContractSplit

        If File.Exists(My.Settings.ConfigPath & "\M3ContactW01.xml") Then GridView1.RestoreLayoutFromXml(My.Settings.ConfigPath & "\M3ContactW01.xml")
        If File.Exists(My.Settings.ConfigPath & "\M3ContactW02.xml") Then GridView2.RestoreLayoutFromXml(My.Settings.ConfigPath & "\M3ContactW02.xml")

    End Sub

    Private Sub SplitContainerControl1_SplitterPositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles SplitContainerControl1.SplitterPositionChanged
        My.Settings.M3ContractSplit = Me.SplitContainerControl1.SplitterPosition
    End Sub


    Private Sub BarButtonItem3_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem3.ItemClick
        'Load data to excel
        Const filename As String = "c:\JLD004_M3Contract_Status.xls"

        If File.Exists(filename) Then
            File.Delete(filename)
        End If

        GridControl1.ExportToXls(filename)

        Dim app = New Microsoft.Office.Interop.Excel.Application
        app.Visible = True

        app.Workbooks.Open(System.IO.Path.GetFullPath(filename))
    End Sub

    Private Sub BarButtonItem4_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem4.ItemClick
        'Load data to excel
        Const filename As String = "c:\JLD004_M3Contract_Date_Procent.xls"

        If File.Exists(filename) Then
            File.Delete(filename)
        End If

        GridControl2.ExportToXls(filename)

        Dim app = New Microsoft.Office.Interop.Excel.Application
        app.Visible = True

        app.Workbooks.Open(System.IO.Path.GetFullPath(filename))
    End Sub
End Class